package cn.pengpeng.sql;
/**
 * 
连续销售
-- 分窗口标记行号
SELECT shopid
	,day
	,sale
	,row_number() OVER (
		PARTITION BY shopid ORDER BY day
		) AS rn
FROM x_shop_sale;


+---------+-------------+-------+-----+--+
| shopid  |     day     | sale  | rn  |
+---------+-------------+-------+-----+--+
| A       | 2015-01-08  | 5     | 1   |
| A       | 2015-01-11  | 15    | 2   |
| A       | 2015-01-12  | 8     | 3   |
| A       | 2015-01-13  | 5     | 4   |
| A       | 2015-02-10  | 4     | 5   |
| A       | 2015-02-11  | 6     | 6   |
| A       | 2015-03-20  | 14    | 7   |
| A       | 2015-03-21  | 6     | 8   |
| B       | 2015-01-12  | 5     | 1   |
| B       | 2015-01-13  | 25    | 2   |
| B       | 2015-02-10  | 10    | 3   |
| B       | 2015-02-11  | 5     | 4   |
| B       | 2015-03-11  | 20    | 5   |
| B       | 2015-03-12  | 25    | 6   |
| C       | 2015-01-09  | 10    | 1   |
| C       | 2015-01-11  | 20    | 2   |
| C       | 2015-01-12  | 30    | 3   |
| C       | 2015-02-13  | 10    | 4   |
| C       | 2015-03-10  | 10    | 5   |
| C       | 2015-03-11  | 20    | 6   |
+---------+-------------+-------+-----+--+

-- 在上表基础上，增加一个字段：每行的日期-行号


SELECT shopid
	,day
	,sale
	,rn
	,date_sub(to_date(day), rn) AS flag
FROM (
	SELECT shopid
		,day
		,sale
		,row_number() OVER (
			PARTITION BY shopid ORDER BY day
			) AS rn
	FROM x_shop_sale
	) tmp;

+---------+-------------+-------+-----+-------------+--+
| shopid  |     day     | sale  | rn  |     flag     |
+---------+-------------+-------+-----+-------------+--+
| A       | 2015-01-08  | 5     | 1   | 2015-01-07  |
| A       | 2015-01-11  | 15    | 2   | 2015-01-09  |
| A       | 2015-01-12  | 8     | 3   | 2015-01-09  |
| A       | 2015-01-13  | 5     | 4   | 2015-01-09  |
| A       | 2015-02-10  | 4     | 5   | 2015-02-05  |
| A       | 2015-02-11  | 6     | 6   | 2015-02-05  |
| A       | 2015-03-20  | 14    | 7   | 2015-03-13  |
| A       | 2015-03-21  | 6     | 8   | 2015-03-13  |
| B       | 2015-01-12  | 5     | 1   | 2015-01-11  |
| B       | 2015-01-13  | 25    | 2   | 2015-01-11  |
| B       | 2015-02-10  | 10    | 3   | 2015-02-07  |
| B       | 2015-02-11  | 5     | 4   | 2015-02-07  |
| B       | 2015-03-11  | 20    | 5   | 2015-03-06  |
| B       | 2015-03-12  | 25    | 6   | 2015-03-06  |
| C       | 2015-01-09  | 10    | 1   | 2015-01-08  |
| C       | 2015-01-11  | 20    | 2   | 2015-01-09  |
| C       | 2015-01-12  | 30    | 3   | 2015-01-09  |
| C       | 2015-02-13  | 10    | 4   | 2015-02-09  |
| C       | 2015-03-10  | 10    | 5   | 2015-03-05  |
| C       | 2015-03-11  | 20    | 6   | 2015-03-05  |
+---------+-------------+-------+-----+-------------+--+


-- 3 按shopid和flag分组，求每组的count数，就是每个shop的连续次数

SELECT shopid
	,count(1) AS cnt
FROM (
	SELECT shopid
		,day
		,sale
		,rn
		,date_sub(to_date(day), rn) AS flag
	FROM (
		SELECT shopid
			,day
			,sale
			,row_number() OVER (
				PARTITION BY shopid ORDER BY day
				) AS rn
		FROM x_shop_sale
		) tmp1
	) tmp2
GROUP BY shopid
	,flag;

+---------+------+--+
| shopid  | cnt  |
+---------+------+--+
| A       | 1    |
| A       | 3    |
| A       | 2    |
| A       | 2    |
| B       | 2    |
| B       | 2    |
| B       | 2    |
| C       | 1    |
| C       | 2    |
| C       | 1    |
| C       | 2    |
+---------+------+--+

-- 4、挑出连续次数>3的数据并对shopid去重

SELECT DISTINCT shopid
FROM (
	SELECT shopid
		,count(1) AS cnt
	FROM (
		SELECT shopid
			,day
			,sale
			,rn
			,date_sub(to_date(day), rn) AS flag
		FROM (
			SELECT shopid
				,day
				,sale
				,row_number() OVER (
					PARTITION BY shopid ORDER BY day
					) AS rn
			FROM x_shop_sale
			) tmp1
		) tmp2
	GROUP BY shopid
		,flag
	) tmp3
WHERE cnt >= 3;
 *
 */
public class LianxuSale {

}
